package com.sample.javabean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.sample.action.form.UserForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 定义用户模型，封装用户属性及方法
 * @author 演示
 *
 */
public class User {
	private Integer id;
	private String userid;
	private String pwd;
	private String fullname;
	private String updatetime;
	private String type;//用户类型，用于区分普通用户与超级管理员
	private String cityno;
	// 实例化JdbcUtils组件，完成加载驱动以及连接数据库操作，获得连接实例
	private JdbcUtils jdbcUtils = new JdbcUtils();
	
	
	
	public User(String userid){
		this.userid =userid;
	}
	/**
	 * 根据用户编号加载用户信息
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_user where userid='"+this.userid+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.userid =rs.getString("userid");
				this.pwd =rs.getString("pwd");
				this.fullname =rs.getString("fullname");
				this.cityno =rs.getString("cityno");
				this.type =rs.getString("type");
			}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载用户信息失败！");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	/**
	 * 添加用户
	 */
	public void addUser() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "insert into t_user(userid,fullname,pwd,cityno,updatetime,type) values('"
					+ userid + "','" + this.fullname + "','" + this.pwd + "','"+this.cityno+"','"+this.updatetime+"','"+this.type+"')";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void editUser() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "update  t_user set fullname='"+this.fullname+"',pwd='"+this.pwd+"',cityno='"+this.cityno+"',updatetime='"+this.updatetime+"',type='"+this.type+"' where userid='"+this.userid+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void delUser() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_user  where userid='"+this.userid+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 用户登录验证
	 * @return
	 */
	public boolean  check() {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs =null;
		boolean flag=false;
		try {
			conn = jdbcUtils.getConn();
		
			// 3:构造插入SQL
			String sql = "select * from t_user where userid=? and pwd=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, this.userid);
			stmt.setString(2, this.pwd);
			// 4:执行SQL并返回结果
		     rs= stmt.executeQuery();
			 flag =rs.next();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt,rs);
		}
		return flag;

	}
	/**
	 * 分页显示用户信息
	 * @return
	 */
	public Pager<User> list(int page,int size,UserForm userForm,String keywords) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<User> users=new Pager<User>();
		users.setPage(page);
		users.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_user where 1=1";
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//模糊查询
			if(null != keywords && "" != keywords && !"".equals(keywords)){
				selectSql = selectSql+"   and (userid like '%"+keywords+"%' or fullname like '%"+keywords+"%')";
			}
			
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 users.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
			
				 int id=rs.getInt("id");
				  String userid=rs.getString("userid");
				  String pwd=rs.getString("pwd");
				  String fullname=rs.getString("fullname");
				  String cityno=rs.getString("cityno");
				  String updatetime=rs.getString("updatetime");
				  String type=rs.getString("type");
				  User user=new User(userid, pwd, fullname, id);
				  user.setCityno(cityno);
				  user.setUpdatetime(updatetime);
				  user.setType(type);
				  users.getDataSets().add(user);
			 }
			 users.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return users;

	}

	public List<Menu> findMenuList(String userid) {

		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		ResultSet rs1 =null;
		List<Menu> menuList =new ArrayList<Menu>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			String sql="select menulistid from t_role where rolename = (select type from t_user where userid = '"+userid+"')";
			 rs =stmt.executeQuery(sql);
			 String  menulistid = "";
			 while(rs.next()){
				menulistid = rs.getString("menulistid");
			}

			String[] s = menulistid.split(",");
			 
			for(int i = 0;i<s.length;i++){
				String sql1 = "select menuname,menuurl from t_menu where id = '"+s[i]+"'";
				rs1 =stmt.executeQuery(sql1);
				String menuname = "";
				String menuurl = "";
				while(rs1.next()){
					menuname = rs1.getString("menuname");
					menuurl = rs1.getString("menuurl");
				}
				Menu menu = new Menu();
				menu.setMenuurl(menuurl);
				menu.setMenuname(menuname);
				menuList.add(menu);
			}
			 
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return menuList;
	}
	
	//定义无参构造函数
	public User(){
		
	}
	//定义带参数构造函数
	public User(String userid,String pwd,String fullname,int id){
		this.userid=userid;
		this.pwd =pwd;
		this.fullname=fullname;
		this.id =id;
	}
	
	public String getUserid() {
		return userid;
	}
	public void setUserid(String userid) {
		this.userid = userid;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getFullname() {
		return fullname;
	}
	public void setFullname(String fullname) {
		this.fullname = fullname;
	}
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUpdatetime() {
		return updatetime;
	}
	public void setUpdatetime(String updatetime) {
		this.updatetime = updatetime;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public String getCityno() {
		return cityno;
	}
	public void setCityno(String cityno) {
		this.cityno = cityno;
	}
}
